package database;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

import manager.IDatabase;
import manager.Manager;

/**
 * Implementation of Database interface that uses a MySQL database to store the information.
 * @author Charles Litfin
 */
public class SQLDatabase implements IDatabase {
	/**
	 * Singleton instance of the SQLDatabase class.
	 */
	private static SQLDatabase instance;
	
	/**
	 * ID of next article
	 */
	private int nextID;
	
	/**
	 * ID of next Story
	 */
	private int nextStory;
	
	/**
	 * Private constructor access to object should be made via the getInstance method.
	 */
	private SQLDatabase(){
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		
			try
			{
				ResultSet result = null;
				Statement stmt = connection.createStatement();
				
				try
				{
					result = stmt.executeQuery("SELECT Story_ID, MAX(Story_ID) FROM story;");
				}
				catch(SQLException e)
				{
					System.out.println(e.getMessage());
				}
				
				result.next();
				if(result == null){
					nextStory = 0;
				}else{
					nextStory = result.getInt("Story_ID");
					nextStory++;
				}
				
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
			}			
			FirstCon con2 = new FirstCon();
			Connection connection2 = con2.returnCon();
			
				try
				{
					ResultSet result = null;
					Statement stmt = connection2.createStatement();
					
					try
					{
						result = stmt.executeQuery("SELECT Article_ID, MAX(Article_ID) FROM article;");
					}
					catch(SQLException e)
					{
						System.out.println(e.getMessage());
					}
					
					result.next();
					if(result == null){
						nextID = 0;
					}else{
						nextID = result.getInt("Article_ID");
						nextID++;
					}
					
				}
				catch(Exception e)
				{
					System.out.println(e.getMessage());
				}
	}
	
	@Override
	public ArticleID storeArticle(Article article) {
		article.setID(new ArticleID(nextID));
//		System.out.println("Successfully accessed the store article function");
		FirstCon con = new FirstCon();
//		System.out.println("Successfully made a connector object");
		Connection connection = con.returnCon();
//		System.out.println("Successfully setup connection");
		if(article.getKeywords() == null){
			List<String> empty = new ArrayList<String>();
			article.setKeywords(empty);
		}
		if(article.getQuotes() == null){
			List<String> empty = new ArrayList<String>();
			article.setQuotes(empty);
		}
		try
		{
			PreparedStatement st = connection.prepareStatement("INSERT INTO article (Article_ID, Story_ID, URL, Source, Date, Title, Article_Keywords, Category, Quotations, Text) VALUES ('" + article.getID().getID() + "'," + "'" + article.getStoryID() + "'," + "'" + article.getUrl() + "'," + "'" + article.getSource() + "'," + "'" + article.getDate().getTime() + "'," + "?, ?," + "'" + article.getCategory() + "'," + "?," + "'" + "" + "');");  
			st.setString(1, article.getTitle());
			String bufferString = "";
			List<String> tempbuff = article.getKeywords();
			for (int j = 0; j <tempbuff.size(); j++){
				bufferString = bufferString + tempbuff.get(j) + ",";
			}
			st.setString(2, bufferString);
			String quoteBuffString = "";
			List<String> quotebuff = article.getKeywords();
			for (int j = 0; j <quotebuff.size(); j++){
				quoteBuffString = quoteBuffString + quotebuff.get(j) + "\"";
			}
			st.setString(3, quoteBuffString);
			st.executeUpdate();
		    nextID++;
		}
		catch(SQLException e)
		{
			System.out.println(e.getMessage());
		}
		return article.getID();
	}

	@Override
	public Article getArticle(ArticleID articleID) {
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		Article temp = new Article();
		
			try
			{
				String sql = "SELECT Article_ID, Story_ID, URL, Source, Date, Title, Article_Keywords, Category, Quotations, Text FROM article WHERE Article_ID = ? ORDER BY Article_ID;";
				PreparedStatement prep = connection.prepareStatement(sql);
				prep.setInt(1, articleID.getID());
				ResultSet result = null;
				//Statement stmt = connection.createStatement();
				
				try
				{
					result = prep.executeQuery();
					result.next();
				}
				catch(SQLException e)
				{
					System.out.println(e.getMessage());
				}
				
				temp.setID(new ArticleID(result.getInt("Article_ID")));
//				System.out.println(temp.getID().getID());
				temp.setStoryID(new StoryID(result.getInt("Story_ID")));
//				System.out.println(temp.getStoryID().getID());
				temp.setUrl(result.getString("URL"));
//				System.out.println(temp.getUrl());
				temp.setSource(result.getString("Source"));
//				System.out.println(temp.getSource());
				temp.setDate(new Date(result.getLong("Date")));
				temp.setTitle(result.getString("Title"));
//				System.out.println(temp.getTitle());
				if(result.getString("Article_Keywords") == null){
					List<String> keywordstemp = new ArrayList<String>();
					temp.setKeywords(keywordstemp);
				}else{
//					temp.setKeywords((List<String>) result.getArray("Article_Keywords"));
					List<String> keywordstemp = new ArrayList<String>(Arrays.asList(result.getString("Article_Keywords").split(",")));
					temp.setKeywords(keywordstemp);
				}
				temp.setCategory(result.getString("Category"));
//				System.out.println(temp.getCategory());
				if(result.getString("Quotations") == null){
					List<String> quotestemp = new ArrayList<String>();
					temp.setQuotes(quotestemp);
					System.out.println("nothing was here");
				}else{
					List<String> quotestemp = new ArrayList<String>(Arrays.asList(result.getString("Quotations").split("\"")));
					temp.setQuotes(quotestemp);
				}
				if(temp.getQuotes() == null){
					System.out.println("This is null");
				}
				//temp.setText(result.getString("Text"));
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
			}
			
			return temp;
	}

	@Override
	public List<Story> getStoriesByDate(long start, long end) {
		ArrayList<Story> stories = new ArrayList<Story>();
		
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		
			try
			{
				ResultSet result = null;
				
				
				try
				{
					PreparedStatement stmt = connection.prepareStatement("SELECT Story_ID, Category, Date, Title FROM story WHERE Date >= ? AND Date <= ? ORDER BY Date;");
					stmt.setLong(1, start);
					stmt.setLong(2, end);
					result = stmt.executeQuery();
				}
				catch(SQLException e)
				{
					System.out.println(e.getMessage());
				}
				
				while(result.next())
				{
					Story temp = new Story();
					temp.setID(new StoryID(result.getInt("Story_ID")));
					temp.setCategory(result.getString("Category"));
					temp.setDate(new Date(result.getLong("Date")));
					temp.setTitle(result.getString("Title"));
					stories.add(temp);
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
			}			
			
		return stories;
	}

	@Override
	public StoryID storeStory(Story story) {
		story.setID(new StoryID(nextStory));
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		
		try
		{
			PreparedStatement st = connection.prepareStatement("INSERT INTO story (Story_ID, Category, Date, Title) VALUES ('" + story.getID().getID() + "'," + "'" + story.getCategory() + "'," + "'" + story.getDate().getTime() + "'," + "?);");  
		    st.setString(1, story.getTitle());
			st.executeUpdate();
		    nextStory++;
		}
		catch(SQLException e)
		{
			System.out.println(e.getMessage());
		}
		return story.getID();
		
	}

	@Override
	public void updateStory(Story story) {
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
					
		try
		{
			PreparedStatement st = connection.prepareStatement("UPDATE story SET Category= ?, Date=?, Title =? WHERE Story_ID = ?;"); 
			st.setString(1, story.getCategory());
			st.setLong(2, story.getDate().getTime());
			st.setString(3, story.getTitle());
			st.setInt(4, story.getID().getID());
		    st.executeUpdate();
		}
		catch(SQLException e)
		{
			System.out.println(e.getMessage());
		}
	}
	
	@Override
	public boolean articleExists(String url) {
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		try{

			ResultSet result = null;
			PreparedStatement stmt = connection.prepareStatement("SELECT Article_ID FROM article WHERE URL = ?;");
			stmt.setString(1, url);
			try
			{
				result = stmt.executeQuery();
				if(result.first()){
					return true;
				}
				else{
					return false;
				}
			}
			catch(SQLException e)
			{
				System.out.println(e.getMessage());
			}
			
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
		}
		return false;	

	}
	
	@Override
	public List<Article> getArticlesInStory(Story story)  {
		ArrayList<Article> returnValue = new ArrayList<Article>();
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		PreparedStatement statement;
		try {
			statement = connection.prepareStatement("SELECT Article_ID FROM article WHERE Story_ID = ?;");
			statement.setInt(1, story.getID().getID());

		ResultSet rs = statement.executeQuery();
		while(rs.next()){
			returnValue.add(this.getArticle(new ArticleID(rs.getInt("Article_ID"))));
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return returnValue;
	}
	
	public boolean clearArticleTable(){
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		try
		{
			Statement stmt = connection.createStatement();
			
			try
			{
				stmt.executeUpdate("TRUNCATE TABLE article;");
				return true;
			}
			catch(SQLException e)
			{
				System.out.println(e.getMessage());
			}		
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
		}
		return false;
	}
	
	public boolean clearStoryTable(){
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		try
		{
			Statement stmt = connection.createStatement();
			
			try
			{
				stmt.executeUpdate("TRUNCATE TABLE story;");
				return true;
			}
			catch(SQLException e)
			{
				System.out.println(e.getMessage());
			}		
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
		}
		return false;
	}

	public boolean clearUserTable(){
		FirstCon con = new FirstCon();
		Connection connection = con.returnCon();
		try
		{
			Statement stmt = connection.createStatement();
			
			try
			{
				stmt.executeUpdate("TRUNCATE TABLE user;");
				return true;
			}
			catch(SQLException e)
			{
				System.out.println(e.getMessage());
			}		
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
		}
		return false;
	}
	
	/**
	 * Retrieves the singleton instance of this class.
	 * @return The singleton instance of this class.
	 */
	public static SQLDatabase getInstance(){
		if (instance == null){
			instance = new SQLDatabase();
		}
		return instance;
	}

	@Override
	public void printArticleTable() {
		System.out.println("not supported");
	}

	@Override
	public void printStoryTable() {
		System.out.println("not supported");
	}


	public static void main(String[] args) throws SQLException{
		SQLDatabase tester = SQLDatabase.getInstance();
		Article testArticle = new Article();
		testArticle.setTitle("test");
		testArticle.setID(new ArticleID(2));
		testArticle.setStoryID(new StoryID(1));
		testArticle.setSource("CNN");
		testArticle.setDate(new Date());
		testArticle.setUrl("http://rss.cnn.com/");
		testArticle.setCategory("world");
		List<String> keylist = new ArrayList<String>();
		keylist.add("test1");
		keylist.add("test2");
		testArticle.setKeywords(keylist);
		List<String> quotelist = new ArrayList<String>();
		quotelist.add("test1");
		quotelist.add("test2");
		testArticle.setKeywords(quotelist);
		tester.storeArticle(testArticle);
		Article a = tester.getArticle(new ArticleID(2));
		System.out.println("-----------Entry-----------");
		System.out.println("Article ID = " + a.getID().getID());
		System.out.println("Title = " + a.getTitle());
		System.out.println("Category = " + a.getCategory());
		System.out.println("Date = " + a.getDate());
		System.out.println("Location = " + a.getLocation());
		System.out.println("Source = " + a.getSource());
		System.out.println("Url = " + a.getUrl());
		System.out.println("Keywords = " + a.getKeywords().toString());
		System.out.println("Quotes = " + a.getQuotes().toString());
		System.out.println("Story ID = " + a.getStoryID().getID());
		
	}
	/**
	public static void main(String[] args){
		//create db to pull from our text based db representation
		FakeDatabase fb = new FakeDatabase();
		//get Map representing the Article's table
		Map<Integer, Article> articles = fb.getArticleTable();
		//grab one of the Articles that currently breaks your code
		Article a = articles.get(5);
		System.out.println("-----------Entry-----------");
		System.out.println("Article ID = " + a.getID().getID());
		System.out.println("Title = " + a.getTitle());
		System.out.println("Category = " + a.getCategory());
		System.out.println("Date = " + a.getDate());
		System.out.println("Location = " + a.getLocation());
		System.out.println("Source = " + a.getSource());
		System.out.println("Url = " + a.getUrl());
		System.out.println("Keywords = " + a.getKeywords());
		System.out.println("Quotes = " + a.getQuotes());
		System.out.println("Story ID = " + a.getStoryID().getID());
		//grab your db object
		IDatabase db = Manager.getDatabase();
		//try to store the Article that breaks your code
		db.storeArticle(a);
		Article article2 = db.getArticle(new ArticleID(1));
		System.out.println("-----------Entry-----------");
		System.out.println("Article ID = " + article2.getID().getID());
		System.out.println("Title = " + article2.getTitle());
		System.out.println("Category = " + article2.getCategory());
		System.out.println("Date = " + article2.getDate());
		System.out.println("Location = " + article2.getLocation());
		System.out.println("Source = " + article2.getSource());
		System.out.println("Url = " + article2.getUrl());
		System.out.println("Keywords = " + article2.getKeywords());
		System.out.println("Quotes = " + article2.getQuotes());
		System.out.println("Story ID = " + article2.getStoryID().getID());
		}*/
}
